﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Module;
using System.Data.SqlClient;
using Qmaker.DAL;
using System.Data;

namespace DLL
{
    public class DStateComment
    {
        #region SQL语句
        private const string SQL_INSERT = "insert into StateComment(StateCommentId,UserStateId,UserPassportId,ToUserPassportId,ToStateCommentId,Body,DateCreated) values (@StateCommentId,@UserStateId,@UserPassportId,@ToUserPassportId,@ToStateCommentId,@Body,@DateCreated)";

        private const string SQL_UPDATE = "update StateComment set UserStateId=@UserStateId,UserPassportId=@UserPassportId,ToUserPassportId=@ToUserPassportId,ToStateCommentId=@ToStateCommentId,Body=@Body,DateCreated=@DateCreated where StateCommentId=@StateCommentId ";

        private const string SQL_DELETE = "delete from StateComment where StateCommentId=@StateCommentId ";



        private const string SQL_SELECT = "select  StateCommentId,UserStateId,UserPassportId,ToUserPassportId,ToStateCommentId,Body,DateCreated from StateComment where UserStateId=@UserStateId";


        #endregion

        #region 公共接口
        /// <summary>
        /// 发表状态回复
        /// </summary>
        /// <param name="stateCommentInfo"></param>
        /// <returns></returns>
        public bool Insert(StateCommentInfo stateCommentInfo)
        {
            if (stateCommentInfo == null)
                throw new ArgumentNullException("stateCommentInfo");

            SqlParameter[] parms = GetInsertParms(stateCommentInfo);
            //parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 更新状态回复
        /// </summary>
        /// <param name="stateCommentInfo"></param>
        /// <returns></returns>
        public bool Update(StateCommentInfo stateCommentInfo)
        {
            if (stateCommentInfo == null || stateCommentInfo.UserPassportId.ToString().Length != 18)
                throw new ArgumentNullException("stateCommentInfo");

            SqlParameter[] parms = GetInsertParms(stateCommentInfo);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 根据Id删除
        /// </summary>
        /// <param name="stateCommentId"></param>
        /// <returns></returns>
        public int Delete(long stateCommentId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@StateCommentId",SqlDbType.BigInt)
                };
            parms[0].Value = stateCommentId;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE, parms);

        }
        /// <summary>
        /// 根据用户发表的状态ID获取回复
        /// </summary>
        /// <param name="userStateId"></param>
        /// <returns></returns>
        public List<StateCommentInfo> GetAllCommentByStatId(long userStateId)
        {
            List<StateCommentInfo> stateComments = new List<StateCommentInfo>();

            StateCommentInfo stateCommentInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@UserStateId",SqlDbType.BigInt)
                };
            parms[0].Value = userStateId;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT, parms))
            {
                while (reader.Read())
                {
                    stateCommentInfo = GetInstanceByDataReader(reader);
                    if (stateCommentInfo != null && stateCommentInfo.StateCommentId != 0)
                        stateComments.Add(stateCommentInfo);
                }
            }
            return stateComments;
        }

        

        #endregion

        #region 私有接口

        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] parms)
        {
            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i].Value == null)
                {
                    parms[i].Value = DBNull.Value;
                }
                if (parms[i].DbType == DbType.DateTime && parms[i].Value.Equals(DateTime.MinValue))
                {
                    parms[i].Value = DBNull.Value;
                }
            }
            return parms;
        }

        private SqlParameter[] GetInsertParms(StateCommentInfo model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@StateCommentId", SqlDbType.BigInt,8),
					new SqlParameter("@UserStateId", SqlDbType.BigInt,8),
                    new SqlParameter("@UserPassportId", SqlDbType.BigInt,8),
					new SqlParameter("@ToUserPassportId", SqlDbType.BigInt,8),
					new SqlParameter("@ToStateCommentId", SqlDbType.BigInt,8),
					new SqlParameter("@Body", SqlDbType.NVarChar,512),
					new SqlParameter("@DateCreated", SqlDbType.DateTime)};
            parameters[0].Value = model.StateCommentId;
            parameters[1].Value = model.UserStateId;
            parameters[2].Value = model.UserPassportId;
            parameters[3].Value = model.ToUserPassportId;
            parameters[4].Value = model.ToStateCommentId;
            parameters[5].Value = model.Body;
            parameters[6].Value = model.DateCreated;

            return SetInsertParmsCheck(parameters);
        }
        private StateCommentInfo GetInstanceByDataReader(SqlDataReader reader)
        {
            StateCommentInfo current = new StateCommentInfo();
            //StateCommentId,UserStateId,UserPassportId,ToUserPassportId,ToStateCommentId,Body,DateCreated
            current.StateCommentId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0);
            current.UserStateId = reader.IsDBNull(1) ? 0 : reader.GetInt64(1);
            current.UserPassportId = reader.IsDBNull(2) ? 0 : reader.GetInt64(2);
            current.ToUserPassportId = reader.IsDBNull(3) ? 0 : reader.GetInt64(3);
            current.ToStateCommentId = reader.IsDBNull(4) ? 0 : reader.GetInt64(4);
            current.Body = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
            current.DateCreated = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6);


            return current;
        }
        #endregion
    }
}
